#!/usr/bin/python
# -*- coding: UTF-8 -*-

################################################################################
#
# Copyright (c) 2020 openEuler.org, Inc. All Rights Reserved
#
################################################################################
"""
Interaction between file and mysql data

Authors:xiaojianghui
Date:    10/22/2020 11:01 AM
"""
import hashlib
import xlrd
import os
import time
import shutil
from dbConnecttion import MysqlConn
from downloadtask import downloadfiletask


def parse_excel(filename):
    """
        parase excel
    :param filename: excel file
    :return pack_name, cpe_pack_name: string, string
    """
    pack_name = []
    cpe_pack_name = []
    try:
        data = xlrd.open_workbook("./mappingexcels/" + filename)
        table_one = data.sheet_by_name("Sheet1")
        row_number = table_one.nrows
        for i in range(1, row_number):
            pack_name.append(table_one.cell(i, 0).value)
            cpe_pack_name.append(table_one.cell(i, 1).value)
    except IndexError as e:
        print("Subscript out of bounds", e)
    except xlrd.XLRDError as e:
        print("Form not found：Sheet1", e)
    return pack_name, cpe_pack_name


def to_mysql():
    """
        Data stored in the database
    """
    downloadfiletask.handle_two()
    files = os.listdir("./mappingexcels")
    if not files:
        print("No package name mapping table")
        return False
    mysql = MysqlConn.Mysql()
    for filename in files:
        with open('./mappingexcels/' + filename, 'rb') as f:
            sha1obj = hashlib.sha1()
            sha1obj.update(f.read())
            hash_value = sha1obj.hexdigest()
            print(filename, hash_value)
            f.close()
        sql = "select file_hash from cve_file_hash where file_name = %s"
        val = (filename,)
        file_hash = mysql.getOne(sql, val)
        if file_hash:
            if hash_value == file_hash['file_hash']:
                print("文件已解析:" + filename)
                f_src = os.path.join("./mappingexcels/" + filename)
                if not os.path.isdir("./oldexcels/"):
                    os.mkdir("./oldexcels/")
                f_dst = os.path.join("./oldexcels/" + filename)
                shutil.move(f_src, f_dst)
                continue
        sql = "insert into cve_file_hash (file_name, file_hash) values (%s, %s)"
        val = (filename, hash_value)
        mysql.insertOne(sql, val)
        mysql.dispose()
        pack_name_list = parse_excel(filename)[0]
        cpe_pack_name_list = parse_excel(filename)[1]
        if pack_name_list and cpe_pack_name_list:
            for j in range(0, len(pack_name_list)):
                create_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
                # Query whether data exists in the database, update the data if it exists and is different
                sql = "select * from cve_package_cpe where packname = %s"
                val = (pack_name_list[j],)
                result = mysql.getOne(sql, val)
                if result:
                    # If the query is not empty, execute the next judgment, if it is empty, insert the data
                    if str(result['packname']) == pack_name_list[j]:
                        if str(result["cpe_packname"]) != cpe_pack_name_list[j]:
                            print("update data:" + pack_name_list[j], cpe_pack_name_list[j])
                            sql = "update cve_package_cpe set cpe_packname=%s, create_time=%s where packname=%s"
                            val = (cpe_pack_name_list[j], create_time, pack_name_list[j])
                            mysql.update(sql, val)
                            mysql.dispose()
                        else:
                            print("the database is up to date+" + pack_name_list[j], cpe_pack_name_list[j])
                else:
                    print("insert data:" + pack_name_list[j], cpe_pack_name_list[j])
                    sql = "insert into cve_package_cpe (packname,cpe_packname,create_time) values (%s,%s,%s)"
                    val = (pack_name_list[j], cpe_pack_name_list[j], create_time)
                    mysql.insertOne(sql, val)
                    mysql.dispose()
        f_src = os.path.join("./mappingexcels/" + filename)
        if not os.path.isdir("./oldexcels/"):
            os.mkdir("./oldexcels/")
        f_dst = os.path.join("./oldexcels/" + filename)
        shutil.move(f_src, f_dst)
    mysql.close()
